package com.visionet.school.app.web.repository.tclass.impl;

import com.visionet.school.app.web.common.utils.PageUtils;
import com.visionet.school.app.web.entity.enums.MemberType;
import com.visionet.school.app.web.entity.tclass.vo.SchoolClassVo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by hxpeng on 2017/3/14.
 */
public class SchoolClassRepositoryImpl {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public Page<SchoolClassVo> findClassPage(SchoolClassVo schoolClassVo, String sortType){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append("SELECT tc.id,tc.class_name,tm.member_name,tc.class_number,tc.class_infomation FROM t_class tc LEFT JOIN t_member_class tmc ON tc.id = tmc.class_id");
        sql.append(" AND tmc.member_type = '"+ MemberType.TEACHER.toKey()+"' AND tmc.is_deleted = 0 LEFT JOIN t_member tm ON tmc.member_id = tm.id");
        sql.append(" and tm.member_type = '"+ MemberType.TEACHER.toKey()+"' and tm.is_deleted = 0 where tc.is_deleted = 0");
        if(StringUtils.isNotBlank(schoolClassVo.getClassName())){
            sql.append(" and tc.class_name like ?");
            params.add("%"+schoolClassVo.getClassName()+"%");
        }
        if(StringUtils.isNotBlank(schoolClassVo.getTeacherName())){
            sql.append(" and tm.member_name like ?");
            params.add("%"+schoolClassVo.getTeacherName()+"%");
        }
        PageRequest page = PageUtils.getPageRequestInfo(schoolClassVo.getPageInfo());
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();
        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<SchoolClassVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<SchoolClassVo>() {
            @Override
            public SchoolClassVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SchoolClassVo entity = new SchoolClassVo();
                entity.setId(rs.getString("id"));
                entity.setClassInfomation(rs.getString("class_infomation"));
                entity.setClassName(rs.getString("class_name"));
                entity.setClassNumber(rs.getInt("class_number"));
                entity.setTeacherName(rs.getString("member_name"));
                return entity;
            }
        });
        Page<SchoolClassVo> cmProductGroupTourPage = new PageImpl<>(list, page, size);
        return cmProductGroupTourPage;
    }

    public SchoolClassVo getClassMember(String id,String memberType){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append("SELECT tc.class_name,tm.id,tm.member_name,tc.class_number,tc.class_infomation FROM t_class tc LEFT JOIN t_member_class tmc ON tc.id = tmc.class_id");
        sql.append(" AND tmc.member_type = '"+memberType+"' AND tmc.is_deleted = 0 LEFT JOIN t_member tm ON tmc.member_id = tm.id");
        sql.append(" and tm.member_type = '"+memberType+"' and tm.is_deleted = 0 where tc.is_deleted = 0");
        if(StringUtils.isNotBlank(id)) {
            sql.append(" and tc.id = ?");
            params.add(id);
        }
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<SchoolClassVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t", params.toArray(), new RowMapper<SchoolClassVo>() {
            @Override
            public SchoolClassVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                SchoolClassVo entity = new SchoolClassVo();
                entity.setClassName(rs.getString("class_name"));
                entity.setTeacherId(rs.getString("id"));
                entity.setTeacherName(rs.getString("member_name"));
                entity.setClassInfomation(rs.getString("class_infomation"));
                return entity;
            }
        });
        return list.get(0);
    }
}
